package com.mymaxis.service;

import com.checkfree.isolutions.customer.persist.Account;
import com.checkfree.isolutions.customer.persist.AccountHomeImpl;
import com.checkfree.isolutions.customer.persist.Merchant;
import com.checkfree.isolutions.customer.persist.MerchantHomeImpl;
import com.checkfree.isolutions.persist.PersistServer;
import com.checkfree.isolutions.property.Properties;
import com.checkfree.isolutions.property.PropertiesConfig;
import com.checkfree.isolutions.statement.persist.Statement;
import com.checkfree.isolutions.statement.persist.StatementHomeImpl;
import com.mymaxis.domain.AccountSubInfo;
import com.mymaxis.domain.UpdateSubscriptionInfoResponse;
import com.mymaxis.ebppx.EbppxAcctDetailsDataBean;
import com.mymaxis.ebppx.EbppxConstants;
import com.mymaxis.ebppx.EbppxEcsUser;
import com.mymaxis.ebppx.MyMaxisAppConnectionPoolServlet;
import com.mymaxis.manager.DBConnectionManager;
import com.mymaxis.utils.DateUtil;
import com.mymaxis.utils.EmailNotification;
import com.mymaxis.utils.EncryptionHelper;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Vector;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class EmailSubscriptionInfo
{
  private final DateFormat df = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
  public static final String passphrase = "X2q3j5k6E7r8l93tm2n7H5qp";

  public EbppxAcctDetailsDataBean getSubscriptionInfo(String accNo, String updateByUser, String updateByChannel)
  {

    EbppxAcctDetailsDataBean acctDataBean = new EbppxAcctDetailsDataBean();

    Connection connection1 = null;
    Connection connection = null;
    Date dateRegisterDate = null;
    Date dateExpiryDate = null;
    Date strRegisterDate = null;
    
    Date currentDate = new Date();
    String emailStatus = "";
    String status = "";
   
    try
    {
      String merchant = "Maxis";
      PersistServer.activate(merchant);
      PersistServer.beginTransaction();
    
      String merchantName = getMerchant(); 
      String strPhysicalBillSubscription = "";
      String email = "";
      //connection =  DBConnectionManager.getDbConnection();
      connection = AcctDetails.getDbConnection();
      String strAcctId = AcctDetails.getAcctId(connection,accNo);
      
      if ((strAcctId == null) || (strAcctId.equals("")))
      {
        Merchant cfiMerchant = MerchantHomeImpl.singleton().findByName(merchant);
        Account account = AccountHomeImpl.singleton().findByExternalKeyAndMerchant(accNo, cfiMerchant);
 
        if (account != null) {
          ////Sytem.out.println("Found in checkfree Db ,add to accountDetails");
          AcctDetails.insertAcctDetails(connection,accNo, accNo, "", "");
          acctDataBean = AcctDetails.getAcctDetails(connection,accNo);

         
        }
        else
        {
          acctDataBean.setAcctId(accNo);
        }

      }
      else
      {
     
    	  acctDataBean = AcctDetails.getLatestEmailDeliveryStatus(connection, accNo);
    	  Date dateRegisteredDate = acctDataBean.getRegisterDate();    
    	  emailStatus = acctDataBean.getEmailStatus();
    
    	  //connection1 = DBConnectionManager.getKenanDbConnection();
          //get KenanConnection
          //String strCategoryDatabase = AcctDetails.getCategoryDatabase(connection1);
         //String cusDatabase = AcctDetails.getCusDatabase(connection1, strCategoryDatabase, accNo);

         // connection1.setAutoCommit(false);
    	 // String kenanAccounNo = AcctDetails.getKenanAccountNo(connection1, strCategoryDatabase, accNo);
         //Sytem.out.println("KenanAcctNO :"+kenanAccounNo);

          
    		if((emailStatus.equals("Update")) ||(emailStatus.equals("Subscription"))){
    			
    		    dateRegisterDate = acctDataBean.getRegisterDate();
    			if (dateRegisterDate != null) {
    				
    				
    				dateExpiryDate = dateRegisterDate;
    				Calendar c = Calendar.getInstance();
    				c.setTime(dateExpiryDate);
    				c.add(Calendar.DATE, 5);
    				dateExpiryDate = c.getTime();
    				////Sytem.out.print("dateExpiryDate :"+dateExpiryDate);
    				////Sytem.out.print("currentDate :"+currentDate);
    				if (currentDate.after(dateExpiryDate)) {
    					
    					 acctDataBean =AcctDetails.getAcctDetails(connection,strAcctId);
    					
    			     }else{
    				     status = "true";	
						 acctDataBean.setEmailStatus("P");
    			    	 acctDataBean.setSMSNo(AcctDetails.getSMSNo(connection,strAcctId));
    			    	 acctDataBean.setSMSSubscription(AcctDetails.getSMSSubscription(connection,strAcctId));
      			
    			     }
    			}
    				
    		}else{
    		
    			acctDataBean =AcctDetails.getAcctDetails(connection,strAcctId);
    		
    		}
    	  
            acctDataBean.setAcctId(accNo);

            if (acctDataBean == null){
        	
              acctDataBean.setAcctId(accNo);
            }

      }

      PersistServer.commitTransaction();
    } catch (Throwable e) {
     
      try {
    	  if (PersistServer.transactionIsActive()) {
				try {
					PersistServer.rollbackTransaction();
				} catch (Exception er) {
				}
			}
			PersistServer.deactivate(); // clean up with deactivate
			////Sytem.out.println("Deactivate the transaction.....");
      }
      catch (Exception localException)
      {
      }
      
    }
    finally
    {
      try
      {
       
    	  if(connection != null){
    		  
    		  AcctDetails.freeDbConnection(connection);
    		  
    	  } 
//    	  if(connection1 != null){
//    		  
//    		  DBConnectionManager.freeKenanDbConnection(connection1);
//    		  
//    	  }
    	  if (PersistServer.transactionIsActive()) {
				try {
					PersistServer.rollbackTransaction();
				} catch (Exception er) {
				}
			}
    	  
			PersistServer.deactivate(); // clean up with deactivate
			////Sytem.out.println("Deactivate the transaction.....");
			
      }catch (Throwable localThrowable2)
      {
      }
    }
    return acctDataBean;
  }

  public String getMerchant()
  {
    String merchantName = "";
    try {
      Properties appCfg = PropertiesConfig.getApplicationSettings();

      if (appCfg.hasProperty("MerchantName"))
        merchantName = appCfg.getProperty("MerchantName");
    }
    catch (Exception e)
    {
     // //Sytem.out.println(e);
    }
    return merchantName;
  }
 
  public UpdateSubscriptionInfoResponse UpdateSubscriptionInfo(String emailAddrs, String emailBillSubscript, String acctNo, String hcSuprssSubscript, String smsAlertFlag, String smsNo, String updateByUser, String updateByChannel)
  {
    String passphrase = "X2q3j5k6E7r8l93tm2n7H5qp";
    EbppxAcctDetailsDataBean acctDataBean = new EbppxAcctDetailsDataBean();
    EbppxAcctDetailsDataBean acctDataBean2 = new EbppxAcctDetailsDataBean();
    UpdateSubscriptionInfoResponse updateResponse = new UpdateSubscriptionInfoResponse();
    EmailNotification emailObj = new EmailNotification();
    AcctDetails acctdetails = new AcctDetails();

    
    boolean booleanEmailAddress = false;
    boolean booleanEmailSubscription = false;
    boolean booleanStopHardCopy = false;
    boolean booleanAccountFound = true;
    String failMsgStopHardCopy = "";
    String failMsgEmailSubscription = "";
    String failMsgEmailAddress = "";
    String strRegisterDateDb = "";
    String emailRegisterStatus = "";
    String emailAddresFrmLog = "";
    String statusFlag = "";
    String statusEmailUnscript = "";
	String statusUnscript ="";
    boolean infoUpdatedFlag = false;
	String strEmailFromLog = "";
    String emailEncrypt = null;
    String acctNoEncrypt = null;
    String acctNameEncrypt = null;
    String emailSentStatus = ""; 
    Date regDateFrmLog  = null;

    String failMsg = "";
    List list = new ArrayList();

    String EMAIL_PATTERN = "^[_A-Za-z0-9-\\+]+(\\.[_A-Za-z0-9-]+)*@[A-Za-z0-9-]+(\\.[A-Za-z0-9]+)*(\\.[A-Za-z]{2,})$";
    String NUMBER_PATTERN = "^[_0-9]";

    String accountName = null;
    String updateStatus = "FAIL";
    String strDate = "";
    String ebillRegStatus = "";
    String strLastModifyDate = "";
    Date registerDateDB = null;
    Date currentDate = null;
    String strRegisterDateNw = "";
    Connection connection1 = null;
    Connection connection = null;
    
    try
    
    {
      PersistServer.activate("Maxis");
      PersistServer.beginTransaction();
      currentDate = new Date();
      strDate = DateUtil.formatDate(new Date());
      strLastModifyDate = DateUtil.formatDate(new Date());
    
      connection = AcctDetails.getDbConnection();
      connection1 = DBConnectionManager.getKenanDbConnection();
      //get KenanConnection
      String strCategoryDatabase = AcctDetails.getCategoryDatabase(connection1);
      String cusDatabase = AcctDetails.getCusDatabase(connection1, strCategoryDatabase, acctNo);

      connection1.setAutoCommit(false);
      connection.setAutoCommit(false);

     
     // //Sytem.out.println("UpdatedByChannel :" + updateByChannel);
     
      //define global variable
      String status = "";
      boolean updateStatusFlag = false;
      boolean emailUpdateFlag = false;
	  boolean SubscriptUpdatedFlag = false;
     
      

      String strEmailDB = "";
      String emailLogAction = "";
      String emailSubDB = "";
      String acctSubDB = "";
      String emailAddrDB = "";
      String smsSubNw = "";
      String smsNoDB = "";
      String emailSubNw = emailBillSubscript;
      String acctSubNw = hcSuprssSubscript;
      String strPhysicalBillSubscription = "";
      String emailAddrNw = emailAddrs;
      String emailSub = emailBillSubscript;
      String smsSub = smsAlertFlag;
      String acctSub = hcSuprssSubscript;
      
/*
      //Sytem.out.println("emailAddrNw :" + emailAddrNw);
      //Sytem.out.println("emailSub :" + emailSub);
      //Sytem.out.println("smsSub :" + smsSub);
      //Sytem.out.println("smsNo :" + smsNo);
      //Sytem.out.println("acctSub :" + acctSub);
      //Sytem.out.println("hcSuprssSubscript :" + hcSuprssSubscript);
      //Sytem.out.println("UpdateByUser    :" + updateByUser);
      //Sytem.out.println("UpdateByChannel :" + updateByChannel);*/
      
      Date dtUpdateDate = new Date();
      String dateString = DateUtil.formatDate(dtUpdateDate);

      if ((acctNo != null) && (!acctNo.equalsIgnoreCase("")))
      {
    	  if ((!hcSuprssSubscript.equalsIgnoreCase("")) && (hcSuprssSubscript != null))
    	  {
    		  if ((hcSuprssSubscript.equalsIgnoreCase("Y")) || (hcSuprssSubscript.equalsIgnoreCase("N")))
    		  {
    			  booleanStopHardCopy = true;
    		  }
    		  else {
    			  failMsgStopHardCopy = "Invalid Flag StopHardCopy";
    			  list.add(failMsgStopHardCopy);
    		  }
    	  }
    	  else
    	  {
    		  failMsgStopHardCopy = "Invalid Flag StopHardCopy";
    		  list.add(failMsgStopHardCopy);
       		}

    	  if ((!emailBillSubscript.equalsIgnoreCase("")) && (emailBillSubscript != null))
    	  {
    		  if ((emailBillSubscript.equalsIgnoreCase("Y")) || (emailBillSubscript.equalsIgnoreCase("N")))
    		  {
    			  booleanEmailSubscription = true;
    		  }
    		  else {
    			  failMsgEmailSubscription = "Invalid Flag EmailSubscription";
    			  list.add(failMsgEmailSubscription);
    		  }
    	  }
    	  else {
    		  failMsgEmailSubscription = "Invalid Flag EmailSubscription";
    		  list.add(failMsgEmailSubscription);
    	  }

    	  if (emailBillSubscript.equalsIgnoreCase("Y"))
    	  {
    		  if ((!emailAddrs.equalsIgnoreCase("")) && (emailAddrs != null) && (emailAddrs != "?"))
    		  {
    			  Pattern p = Pattern.compile(EMAIL_PATTERN);
    			  Matcher m = p.matcher(emailAddrs);

    			  if (m.find())
    			  {
    				  booleanEmailAddress = true;
    			  }
    			  else
    			  {
    				  failMsgEmailAddress = "Invalid Email Address";
    				  list.add(failMsgEmailAddress);
    			  }

    		  }
    		  else
    		  {
    			  failMsgEmailAddress = "Invalid Email Address";
    			  list.add(failMsgEmailAddress);
    		  }

    	  }
    	  else
    	  {
    		  booleanEmailAddress = true;
    	  }

       // //Sytem.out.println("list:" + list);

        ////Sytem.out.println("list size:" + list.size());

        if (list.size() != 0)
        {
          for (int i = 0; i < list.size(); i++)
          {
            if ((list.size() == 1) || (list.size() == i + 1))
            {
              failMsg = failMsg + list.get(i).toString();
            }
            else
            {
              failMsg = failMsg + list.get(i).toString() + ",";
            }

          }

        }

        if ((booleanEmailSubscription) && (booleanStopHardCopy) && (booleanEmailAddress))
        {
        	
        	
            //set Y and N to 1 or 0
            if (emailSub != null) {
            	
            	if (emailSub.equalsIgnoreCase("Y"))
            	{
            		emailSubNw = "1";
            	}
            	else if (emailSub.equalsIgnoreCase("N"))
            	{
            		emailSubNw = "0";
            	}
            }

            if (acctSub != null){
            
            	if (acctSub.equalsIgnoreCase("Y"))
            	{
            		acctSubNw = "1";
            	}
            	else if (acctSub.equalsIgnoreCase("N"))
            	{
            		acctSubNw = "0";
            	}
            }

            if (smsSub != null) {
            	
            	if (smsSub.equalsIgnoreCase("Y"))
            	{
                smsSubNw = "1";
            	}
            	else if (smsSub.equalsIgnoreCase("N"))
            	{
            		smsSubNw = "0";
            	}

            }
          
          //find Account in AccountDetails table 
          String strAcctId = AcctDetails.getAcctId(connection,acctNo);
         // //Sytem.out.println("strAcctId found :" + strAcctId);
          
          //if strAcctID is null , account is not found in AccountDetails Table
          if ((strAcctId == null) || (strAcctId.equals(""))){
          
        	  //check Account in accountTable 
        	  Merchant cfiMerchant = MerchantHomeImpl.singleton().findByName("Maxis");
           	  Account account = AccountHomeImpl.singleton().findByExternalKeyAndMerchant(acctNo, cfiMerchant);
           	  
           	  //if found this accountNo in Account Table ,insert to AccountDetails table ..else insert to account and accountDetails table
           	  if (account != null) {
           		 // //Sytem.out.println("Found in checkfree Db ,add to accountDetails");
           		  AcctDetails.insertAcctDetails(connection,acctNo, acctNo, "", "");
           		  booleanAccountFound = true;
           	  }
           	  else
           	  {
           		  EbppxEcsUser ecsUser = new EbppxEcsUser();
           		  ecsUser.setAccount(acctNo);
           		  Vector cfiAccoountunts = ecsUser.getAccounts();
           		  AcctDetails.insertAcctDetails(connection,acctNo, acctNo, "", "");
           		  booleanAccountFound = true;
           	  }

          }

          
          //Account found in both Account and AccountDetails table
          if (booleanAccountFound){
          
        	  
        	 //get AccountInfo from AccountDetails table 
        	  acctDataBean = AcctDetails.getAcctDetails(connection,acctNo);
        	  
        	  //get AccountInfo from EbillRegisterLog table
        	  acctDataBean2 = AcctDetails.getLatestEmailDeliveryStatus(connection, acctNo);
        	  emailRegisterStatus = acctDataBean2.getEmailStatus();
        	 
            
        	  //if emailRegisterStatus = "" , this is new user 
        	  if(!emailRegisterStatus.equals("")){ 
   
        		  
        		  
        		  if(emailRegisterStatus.equals("Verification")||emailRegisterStatus.equals("unsubscription")){
            	
        			  infoUpdatedFlag = true;
        			  SubscriptUpdatedFlag = true;
        			  strEmailFromLog = acctDataBean.getEmailAdd();
            	
        		  }
        		  //user already subscription ebill and want to update email address
        		  if((emailRegisterStatus.equals("Subscription")) || (emailRegisterStatus.equals("Update"))){
        			  
        			  //if emailSub = 0 ,mean user perform unsubscription 
        			  if(emailSubNw.equals("1")){
        				  
        				  //check for expire date 
        				  regDateFrmLog = acctDataBean2.getRegisterDate();
        				  Calendar c = Calendar.getInstance();
        				  c.setTime(regDateFrmLog);
        				  c.add(Calendar.DATE, 5);
        				  regDateFrmLog = c.getTime();
        				 // //Sytem.out.print("dateExpiryDate :"+regDateFrmLog);
        				 // //Sytem.out.print("currentDate :"+currentDate);
        				  //currentDate
        				  if(currentDate.after(regDateFrmLog)){
        					  ////Sytem.out.println("Date Expire");
        					  
        					  //if Expire get emailAdress from AccountDetails table
        					  emailAddresFrmLog =acctDataBean.getEmailAdd();
        					  emailUpdateFlag = true;
                		
    					
        				  }else{
    					      //if havent expire get the emailAddress from ebillregisterLog table
        					  emailAddresFrmLog =acctDataBean2.getEmailAdd();
    					
        				  }
    				
        				  infoUpdatedFlag = true;
            		
        				  strEmailFromLog = emailAddresFrmLog;
        				 // //Sytem.out.println("user can update only");
            		}
            	}
            }else{
            	
            	////Sytem.out.println("Dunt have email log : first User ");
            	strEmailFromLog = acctDataBean.getEmailAdd();
            	infoUpdatedFlag = false;
            	SubscriptUpdatedFlag = false;
            	
            }
        	  
        	//retrieve info from DB 
            strEmailDB = acctDataBean.getEmailAdd();
            emailSubDB = acctDataBean.getEmailSubscription();
            registerDateDB = acctDataBean.getRegisterDate();
            emailAddrDB = acctDataBean.getEmailAdd();

            if (registerDateDB != null) {
            	
              strRegisterDateNw = this.df.format(registerDateDB);
            }

     
            if (emailAddrNw != null){
            	emailEncrypt = EncryptionHelper.encrypt(emailAddrNw, "X2q3j5k6E7r8l93tm2n7H5qp");
             
            }

            if (acctNo != null){
              acctNoEncrypt = EncryptionHelper.encrypt(acctNo, "X2q3j5k6E7r8l93tm2n7H5qp");
            }
            
            Merchant cfiMerchant = MerchantHomeImpl.singleton().findByName("Maxis");
            Account account = AccountHomeImpl.singleton().findByExternalKeyAndMerchant(acctNo, cfiMerchant);
            Statement cfiStatement = null;
            
            if ((account != null) && (!account.equals(""))) {
            	
            	cfiStatement = getStatementHome().findTheLatestStatementForAccount(account);
            
            }

            if (cfiStatement != null) {
              accountName = cfiStatement.getUserField1();
            }
            else {
              accountName = "Customer";
            }
            

            if (accountName != null)
            {
              acctNameEncrypt = EncryptionHelper.encrypt(accountName, "X2q3j5k6E7r8l93tm2n7H5qp");
            }

      
            if (registerDateDB != null)
            {
              strRegisterDateDb = this.df.format(registerDateDB);
            }

            
            
           // //Sytem.out.println("infoUpdatedFlag :"+infoUpdatedFlag);
           // //Sytem.out.println("SubscriptUpdatedFlag :"+SubscriptUpdatedFlag);
            
            //if both false mean first user
            if((!infoUpdatedFlag) && (!SubscriptUpdatedFlag)){
			
              if(emailSubNw.equals("1")){
				if((emailAddrDB != null) && (emailAddrDB != "")){
					
					if(!emailAddrDB.equals(emailAddrNw)){
					   
						emailUpdateFlag = true;
					}	
				}
			
		    	if((emailSubDB != null) && (emailSubDB != "")){
					
		    		if(!emailSubDB.equals("1")){
		    		
		    			SubscriptUpdatedFlag = true;
		    		}
					
		    	}
              }
			}

            String kenanAccounNo = AcctDetails.getKenanAccountNo(connection1, strCategoryDatabase, acctNo);
           // //Sytem.out.println("KenanAcctNO :"+kenanAccounNo);

        
   
        	// If there is a changes in the subscription bit for SMS notification
			if(!smsSub.equals(smsSubNw)){
				if (smsSub.equals("0") && smsSubNw.equals("1") && !smsSubNw.equals("")){
					AcctDetails.logActivity(connection, null, EbppxConstants.SMS_NOTIFY_SUBSCRIPTION, acctNo, updateByUser, updateByChannel, EbppxConstants.STATUS_CHANGED, dateString, "", "", "", "", "");
			        
				}else if (smsSub.equals("1") && smsSubNw.equals("0")){
					AcctDetails.logActivity(connection, null, EbppxConstants.SMS_NOTIFY_UNSUBSCRIPTION,acctNo, updateByUser, updateByChannel, EbppxConstants.STATUS_CHANGED, dateString, "", "", "", "", "");
				
				}
			
				AcctDetails.updateSMSSubscription(connection, acctNo,smsSubNw);
				acctDataBean.setSMSSubscription(smsSubNw);
			}
			
			if((smsNoDB != smsNo) && (smsSubNw.equals("1"))){
				
				AcctDetails.updateSMSNo(connection, acctNo, smsNo);
				acctDataBean.setSMSNo(smsNo);
				AcctDetails.logActivity(connection, null, EbppxConstants.SMS_NO_CHANGE,acctNo, updateByUser, updateByChannel, EbppxConstants.STATUS_CHANGED, dateString, "", smsNo, smsNoDB, "", "");
			
			}
			
			////Sytem.out.println("emailAddrNw :"+emailAddrNw);
            if(((!emailAddrNw.equals(strEmailFromLog)) && infoUpdatedFlag && !emailAddrNw.equals("")) || (emailUpdateFlag && !emailAddrNw.equals(""))){
        		
            	
            	if(emailAddrDB.equals("")){
            		  
            		AcctDetails.logUserActivity("AC001", acctNo, "user", updateByChannel, "CHANGED", strLastModifyDate, "", emailAddrNw, strEmailFromLog, "", "");
            	}else{
            		AcctDetails.logUserActivity("AC001", acctNo, "user", updateByChannel, "CHANGED", strLastModifyDate, "", emailAddrNw, emailAddrDB, "", "");
            	}
            	statusFlag="true";
				status="subscript"; 
				////Sytem.out.println("EMAIL CHANGED");
						
		
            }

        
          /*  if(!AcctDetails.checkIfPhysicalBillSubsExist(connection1,cusDatabase,acctNo,strCategoryDatabase)){//this is used to check if the data exist in the info_cust_text table
					
				//Sytem.out.println("Kenan for phsical no exits ");
				if(!emailSubDB.equals(emailSubNw)){
					
					AcctDetails.insertInfoCustText(connection1, cusDatabase,EbppxConstants.NO_PHYSICAL_BILL_UNSUBSCRIPTION,kenanAccounNo);
						
				}
				
				
				
			}*/
     
          // if(AcctDetails.checkIfEbillSubsExist(connection1,cusDatabase,acctNo,strCategoryDatabase)){//this is used to check if the data exist in the info_cust_text table
				// If there is a changes in the subscription bit for Email bill delivery
				////Sytem.out.println("emailSubNw :"+emailSubNw);
				////Sytem.out.println("emailRegisterStatus :"+emailRegisterStatus);
				////Sytem.out.println("emailSubDB :"+emailSubDB);
					if ((emailSubDB.equals("0"))&&(!emailAddrNw.equals("")) && (!emailSubNw.equals("0"))&&(SubscriptUpdatedFlag)){
						////Sytem.out.println("EMAIL SUBSCRIPT ");
						status="subscript"; 
						statusFlag="true";
						AcctDetails.logActivity(connection, null, EbppxConstants.EMAIL_DELIVERY_SUBSCRIPTION, acctNo, updateByUser, updateByChannel, EbppxConstants.STATUS_CHANGED, dateString, "", emailAddrNw, "", "", "EMAILSUBWEB");
						//AcctDetails.logActivity(connection, stmt, EbppxConstants.EMAIL_DELIVERY_SUBSCRIPTION, strAcctExtKey, ecsUser.getUserId(), EbppxConstants.CUSTOMER, EbppxConstants.STATUS_CHANGED, dateString, "", strEmailAddrNew, "", "", "");
						acctDataBean.setEmailSubscription(emailSubNw);
					}else if ((emailSubNw.equals("0") && (!emailRegisterStatus.equals("")) && !emailRegisterStatus.equals("unsubscription")) ||((emailSubNw.equals("0")) && (emailSubDB.equals("1")))){
						
						statusEmailUnscript="true";		
						statusUnscript="unsubscription";
						AcctDetails.logActivity(connection, null, EbppxConstants.EMAIL_DELIVERY_UNSUBSCRIPTION, acctNo, updateByUser, updateByChannel, EbppxConstants.STATUS_CHANGED, dateString, "", emailAddrDB, "", "", "EMAILUNSUBWEB");
						if((!emailAddrDB.equals("")) && (!emailSubDB.equals("0")) &&(!emailSubDB.equals(""))){
							
							if (!updateByChannel.equals("SSO")){
								
								String emailDBEncrypt = EncryptionHelper.encrypt(strEmailDB,passphrase);
					       		emailSentStatus = emailObj.sendEmailNotification(emailDBEncrypt,strEmailDB,acctNoEncrypt,acctDataBean.getAcctId(),"",statusUnscript,accountName,acctNameEncrypt,emailLogAction,updateByChannel);
							}else{
								AcctDetails.logEmailDelivery(connection,acctNo, strEmailFromLog,dateString, updateByUser, EbppxConstants.CUST_UNSUBSCRIPTION_ACTION, EbppxConstants.CUST_UNSUBSCRIPTION_ACTION_VALUE,updateByChannel);
							}
						}else{
							
							AcctDetails.logEmailDelivery(connection,acctNo, strEmailFromLog,dateString, updateByUser, EbppxConstants.CUST_UNSUBSCRIPTION_ACTION, EbppxConstants.CUST_UNSUBSCRIPTION_ACTION_VALUE,updateByChannel);
						
							
						   
						}
						
						acctDataBean.setEmailSubscription(emailSubNw);
						
						//AcctDetails.updateInfoCustText(connection1, cusDatabase,EbppxConstants.NO_PHYSICAL_BILL_UNSUBSCRIPTION, kenanAccounNo);
				    	
				    	if(AcctDetails.checkIfEbillSubsExist(connection1,cusDatabase,acctNo,strCategoryDatabase)){
				    		
				    		AcctDetails.updateInfoCustText(connection1, cusDatabase,EbppxConstants.EMAIL_DELIVERY_UNSUBSCRIPTION, kenanAccounNo);
				    	}
				    	
				    	if(AcctDetails.checkIfPhysicalBillSubsExist(connection1,cusDatabase,acctNo,strCategoryDatabase)){
				    		
				    		AcctDetails.updateInfoCustText(connection1, cusDatabase,EbppxConstants.NO_PHYSICAL_BILL_UNSUBSCRIPTION, kenanAccounNo);
				    	}
				    	
				    	AcctDetails.updateAcctInfo(connection, acctNo, "0", dateString,"", updateByChannel, updateByUser);
					}
		
            
            if (status == "subscript")
            {
             
            	if (!updateByChannel.equals("SSO"))
            	{
            	  
            			if((infoUpdatedFlag ||emailUpdateFlag)){
            	 		
            				emailLogAction="update";
            			
            			}
            			emailSentStatus = emailObj.sendEmailNotification(emailEncrypt, emailAddrNw, acctNoEncrypt, acctNo, updateByUser, status, accountName, acctNameEncrypt, emailLogAction, updateByChannel);

            			////Sytem.out.println("emailSentStatus :" + emailSentStatus);

               
                  }
                 else {
            	  		////Sytem.out.println(" Update by SSO only ");
            	  		emailSubNw = "1";
            	  		emailSentStatus = "success";
            	  		////Sytem.out.println("updateByChannel :"+updateByChannel);
            	  		AcctDetails.logEmailDelivery(connection,acctNo, emailAddrNw,dateString, updateByUser, EbppxConstants.CUST_VERIFICATION_ACTION, EbppxConstants.CUST_VERIFICATION_ACTION_ACTION,updateByChannel);
            	  			
            	  		if(AcctDetails.checkIfEbillSubsExist(connection1,cusDatabase,acctNo,strCategoryDatabase)){
				    		
				    		AcctDetails.updateInfoCustText(connection1, cusDatabase,EbppxConstants.EMAIL_DELIVERY_SUBSCRIPTION, kenanAccounNo);
				    	}else{
				    		
				    		AcctDetails.insertInfoCustText(connection1, cusDatabase,EbppxConstants.EMAIL_DELIVERY_SUBSCRIPTION,kenanAccounNo);
				    	}
				    	
            	  		if(AcctDetails.checkIfPhysicalBillSubsExist(connection1,cusDatabase,acctNo,strCategoryDatabase)){
            	  			
            	  			AcctDetails.updateInfoCustText(connection1, cusDatabase, EbppxConstants.NO_PHYSICAL_BILL_SUBSCRIPTION, kenanAccounNo);
            	  		
            	  		}else{
            	  			
            	  			AcctDetails.insertInfoCustText(connection1, cusDatabase,EbppxConstants.NO_PHYSICAL_BILL_SUBSCRIPTION,kenanAccounNo);
            	  		}
            	  		
            	  		updateStatusFlag = AcctDetails.updateAllAcctDetailsCsr(connection, acctNo, emailSubNw, smsSubNw, emailAddrNw, smsNo, dateString, dateString, updateByUser, updateByChannel);
                 
            	  	
              		}
            	}
            
      

          }
          else
          {
            failMsg = failMsg + "Account No found";
          }
        }
      }
      else {
        failMsg = failMsg + "Account No is empty";
      }
     // //Sytem.out.println("emailSentStatus :" + emailSentStatus);
      ////Sytem.out.println("updateStatusFlag :" + updateStatusFlag);
      connection.commit();
      connection.setAutoCommit(true);
      connection1.commit();
      connection1.setAutoCommit(true);
      updateStatus = "SUCC";
      PersistServer.commitTransaction();
    } catch (Throwable e) {
      e.printStackTrace();
      updateStatus = "FAIL";
      ////Sytem.out.println(failMsg);
      try
      {
        PersistServer.rollbackTransaction();
      } catch (Exception ex) {
        ex.printStackTrace();
      }

      updateResponse.setExtAcctNo(acctNo);
      updateResponse.setUpdateStatus(updateStatus);
      updateResponse.setFailMsg(failMsg.toString());
      try
      {
        if (connection1 != null) {
           DBConnectionManager.freeKenanDbConnection(connection1);
         
        }
        if(connection != null){
        	
        	 AcctDetails.freeDbConnection(connection);
        }
        if (PersistServer.transactionIsActive()) {
			try {
				PersistServer.rollbackTransaction();
			} catch (Exception er) {
			}
			PersistServer.deactivate(); // clean up with deactivate
		}
	
		////Sytem.out.println("Deactivate the transaction.....");
        
      }
      catch (SQLException localSQLException)
      {
      }
      catch (Throwable localThrowable1)
      {
      }
    }
    finally
    {
      updateResponse.setExtAcctNo(acctNo);
      updateResponse.setUpdateStatus(updateStatus);
      updateResponse.setFailMsg(failMsg.toString());
      try
      {
        if (connection1 != null) {
          DBConnectionManager.freeKenanDbConnection(connection1);
        
        }
        if(connection != null){
        	
        	acctdetails.freeDbConnection(connection);
        }
        if (PersistServer.transactionIsActive()) {
			try {
				PersistServer.rollbackTransaction();
			} catch (Exception er) {
			}
		}
		PersistServer.deactivate(); // clean up with deactivate
		////Sytem.out.println("Deactivate the transaction.....");
      }
      catch (SQLException localSQLException1)
      {
      }
      catch (Throwable localThrowable2) {
      }
    }
    return updateResponse;
  }

  public StatementHomeImpl getStatementHome()
  {
    return StatementHomeImpl.singleton();
  }

  
}